Несколько прошлых месяцев развлекательное приложение Procrastinate Pro+ постоянно несёт убытки - в привлечение пользователей была вложена куча денег, а толку никакого. Нужно разобраться в причинах этой ситуации.
Считаем, что дата анализа – 1 ноября 2019 года. Срок окупаемости, который смотрим, 2 недели после привлечения пользователей.
Лог посещения приложения новыми пользователями, зарегистрировавшимися в период с 2019-05-01 по 2019-10-27. Таблица visits_log_short:
User Id — уникальный идентификатор пользователя
Device — категория устройства пользователя
Session start — дата и время начала сессии
Session End — дата и время окончания сессии
Channel — идентификатор рекламного источника, из которого пришел пользователь
Region - страна пользователя
Лог покупок в приложении за период. Таблица orders_log_short:
User Id — уникальный id пользователя, который сделал заказ
Event Dt — дата и время покупки
Revenue — выручка
Статистика рекламных расходов. Таблица costs_short:
Channel — идентификатор рекламного источника
Dt — дата
Costs — затраты на этот рекламный источник в этот день
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from scipy import stats
from datetime import timedelta, datetime
from matplotlib.ticker import StrMethodFormatter
# зададим дефолтные настройки отображения таблиц и графиков
pd.set_option('display.float_format', '{:_.2f}'.format)
#pd.set_option('display.max_columns', 100)
pd.set_option('display.max_rows', 30)
pd.set_option('display.min_rows', 30)
plt.rcParams['font.size'] = '12'
plt.rcParams['figure.figsize'] = (12, 6)
# пути до данных
try:
Path("/datasets").exists()
VISITS = '/datasets/visits_info_short.csv'
ORDERS = '/datasets/orders_info_short.csv'
COSTS = '/datasets/costs_info_short.csv'
except:
VISITS = 'visits_info_short.csv'
ORDERS = 'orders_info_short.csv'
COSTS = 'costs_info_short.csv'
Загрузим данные, посмотрим, всё ли ок.
# сразу зададим нужный нам формат данных и поправим названия
visits = pd.read_csv(VISITS,
parse_dates=['Session Start', 'Session End'],
# dtype={'Region': 'category',
# 'Device': 'category',
# 'Channel': 'category'}
)
visits.columns = [name.lower().replace(' ', '_') for name in visits.columns]
# проверим, что нет полных дублей
visits.duplicated().sum()
0
visits.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 309901 entries, 0 to 309900 Data columns (total 6 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 user_id 309901 non-null int64 1 region 309901 non-null object 2 device 309901 non-null object 3 channel 309901 non-null object 4 session_start 309901 non-null datetime64[ns] 5 session_end 309901 non-null datetime64[ns] dtypes: datetime64[ns](2), int64(1), object(3) memory usage: 14.2+ MB
visits.sample(10)
| user_id | region | device | channel | session_start | session_end | |
|---|---|---|---|---|---|---|
| 105985 | 717425970816 | United States | PC | organic | 2019-08-12 22:20:27 | 2019-08-12 23:37:28 |
| 286446 | 302550627742 | France | PC | OppleCreativeMedia | 2019-09-25 16:23:51 | 2019-09-25 17:22:17 |
| 3075 | 34191387246 | United States | iPhone | organic | 2019-05-05 09:34:37 | 2019-05-05 10:34:24 |
| 210880 | 286060620548 | UK | iPhone | organic | 2019-05-09 17:00:35 | 2019-05-09 17:06:28 |
| 238433 | 982808829207 | Germany | PC | WahooNetBanner | 2019-07-02 02:47:32 | 2019-07-02 02:53:24 |
| 216166 | 781102935572 | France | Android | organic | 2019-05-19 08:15:07 | 2019-05-19 08:30:15 |
| 64227 | 318687864260 | United States | iPhone | TipTop | 2019-07-04 19:32:26 | 2019-07-04 20:50:53 |
| 293893 | 585164741892 | Germany | iPhone | WahooNetBanner | 2019-10-05 18:13:33 | 2019-10-05 18:24:33 |
| 121133 | 393524213128 | United States | Android | RocketSuperAds | 2019-08-25 01:08:25 | 2019-08-25 02:09:48 |
| 188995 | 278367887408 | United States | PC | organic | 2019-10-16 10:41:45 | 2019-10-16 10:56:07 |
# добавим в сет продолжительность сессий и посмотрим, как они распределяются
visits['session_duration'] = visits.session_end - visits.session_start
(visits.session_duration.dt.seconds / 60).hist(bins=50)
plt.title('Распределение сессий по продолжительности')
plt.xlabel('Минуты')
plt.show()
(visits['session_duration'].dt.seconds / 60).describe()
count 309_901.00 mean 30.00 std 30.05 min 0.00 25% 8.62 50% 20.73 75% 41.62 max 370.95 Name: session_duration, dtype: float64
for sec in [0, 5]:
print(f'Количество сессий длиной {sec} и меньше секунд: {(visits.session_duration.dt.seconds <= sec).sum()}')
Количество сессий длиной 0 и меньше секунд: 163 Количество сессий длиной 5 и меньше секунд: 1012
В целом сессии выглядят правдоподобно. У нас есть небольшое количество нулевых или околонулевых сессий – по-хорошему, надо было бы их убить. Но тогда может "поехать" расчет денег – если нулевая сессия окажется первым визитом, "съедет" когорта пользователя. Оставим их.
Но если бы это был реальный кейс, стоило бы выяснить, откуда у нас в данных нулевые сессии – по идее в выгрузке логов и уж тем более в рекламной статистике их уже не должно быть.
Загрузим данные и посмотрим, всё ли ок.
orders = pd.read_csv(ORDERS, parse_dates=['Event Dt'])
orders.columns = [name.lower().replace(' ', '_') for name in orders.columns]
orders.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 40212 entries, 0 to 40211 Data columns (total 3 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 user_id 40212 non-null int64 1 event_dt 40212 non-null datetime64[ns] 2 revenue 40212 non-null float64 dtypes: datetime64[ns](1), float64(1), int64(1) memory usage: 942.6 KB
visits.duplicated().sum()
0
orders.describe()
| user_id | revenue | |
|---|---|---|
| count | 40_212.00 | 40_212.00 |
| mean | 499_029_531_203.23 | 5.37 |
| std | 286_093_675_967.17 | 3.45 |
| min | 599_326.00 | 4.99 |
| 25% | 251_132_440_436.75 | 4.99 |
| 50% | 498_283_972_665.00 | 4.99 |
| 75% | 743_332_711_780.00 | 4.99 |
| max | 999_895_427_370.00 | 49.99 |
orders.revenue.value_counts().sort_index()
4.99 38631 5.99 780 9.99 385 19.99 204 49.99 212 Name: revenue, dtype: int64
Видимо, у нас в приложении есть набор покупок по фиксированным ценам. Всего 5 уровней цены.
Посмотрим, всё ли ок – построим график суммарной выручки и количества покупок по дням.
fig, ax = plt.subplots(figsize=(15, 7))
(orders
.pivot_table(index=orders.event_dt.astype('datetime64[D]'),
values='revenue',
aggfunc='sum')
.rename({'revenue': 'Выручка за день'}, axis=1)
.plot(ax=ax)
)
ax2 = ax.twinx()
(orders
.pivot_table(index=orders.event_dt.astype('datetime64[D]'),
values='revenue',
aggfunc='count')
.rename({'revenue': 'Количество покупок за день'}, axis=1)
.plot(ax=ax2, color='r')
)
lines, labels = ax.get_legend_handles_labels()
lines2, labels2 = ax2.get_legend_handles_labels()
ax2.legend(lines + lines2, labels + labels2, loc=0)
ax.grid()
ax.set_ylabel('$')
ax2.set_ylabel('Количество покупок')
ax.set_title('Выручка и количество покупок по дням')
plt.show()
Кажется, всё ок. Количество пользователей у нас в датасете растёт – и количество покупок и выручка тоже. Средний размер покупки стабильный.
Загрузим и проверим данные.
costs = pd.read_csv(COSTS, parse_dates=['dt'], dtype={'Channel': 'category'})
costs.columns = costs.columns.str.lower()
costs.duplicated().sum()
0
costs.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 1800 entries, 0 to 1799 Data columns (total 3 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 dt 1800 non-null datetime64[ns] 1 channel 1800 non-null category 2 costs 1800 non-null float64 dtypes: category(1), datetime64[ns](1), float64(1) memory usage: 30.4 KB
# построим график ежедневных затрат по каналам
fig, ax = plt.subplots(figsize=(15, 7))
(costs
.pivot_table(index=costs.dt.astype('datetime64[D]'),
values='costs',
aggfunc='sum',
columns='channel')
.plot(ax=ax, kind='area', stacked=True)
)
ax.set_title('Дневные затраты на привлечение пользователей в разбивке по каналам')
ax.set_ylabel('Затраты на привлечение за день, $')
ax.set_xlabel('')
plt.show()
Выглядит реалистично – есть два основных потребителя рекламных денег: ТипТоп и Фейсбум. Вливания в остальные каналы гораздо меньше.
Для удобства сведём все данные в одну сводную таблицу. Один пользователь – одна строка.
Чтобы все маркетинговые показатели можно было корректно сравнивать между пользователями, пришедшими в разное время, ограничим историю жизни каждого пользователя двумя неделями с момента привлечения.
Даты заменим на дни жизни. День номер 1 – это день привлечения.
users = (visits
.pivot_table(index='user_id',
aggfunc={'region': 'first',
'device': 'first',
'channel': 'first',
'session_start': 'min'})
.reset_index()
)
users.rename(columns={'session_start': 'first_visit'}, inplace=True)
users.first_visit = users.first_visit.astype('datetime64[D]')
users.head()
| user_id | channel | device | region | first_visit | |
|---|---|---|---|---|---|
| 0 | 599326 | FaceBoom | Mac | United States | 2019-05-07 |
| 1 | 4919697 | FaceBoom | iPhone | United States | 2019-07-09 |
| 2 | 6085896 | organic | iPhone | France | 2019-10-01 |
| 3 | 22593348 | AdNonSense | PC | Germany | 2019-08-22 |
| 4 | 31989216 | YRabbit | iPhone | United States | 2019-10-02 |
Последний день данных у нас 31 октября. Значит, если мы хотим смотреть двухнедельных пользователей, можем взять всех, кто пришёл не позднее 18 октября.
LAST_DAY = (visits.session_start.astype('datetime64[D]').max() - timedelta(days=13))
LAST_DAY
Timestamp('2019-10-18 00:00:00')
users = users[users.first_visit <= LAST_DAY]
# добавим пользователям стоимость их привлечения
cac_by_channel = (users
.pivot_table(index=['first_visit', 'channel'],
values='user_id',
aggfunc='count')
.reset_index()
.merge(costs
.pivot_table(index=['dt', 'channel'],
values='costs',
aggfunc='sum')
.reset_index(),
how='left', left_on=['first_visit', 'channel'], right_on=['dt', 'channel'])
)
cac_by_channel.costs.fillna(0, inplace=True)
cac_by_channel['cac'] = cac_by_channel.costs / cac_by_channel.user_id
users = (users
.merge(cac_by_channel[['first_visit', 'channel', 'cac']],
how='left',
on=['first_visit', 'channel'])
)
# рассчитаем для каждой сессии её лайфтайм относительно первого дня.
# 1-ый лайфтайм – это день привлечения.
# уберём из сессий всё, что после 14 дня.
visits_2_weeks = visits.merge(users[['user_id', 'first_visit']], how='right', on='user_id')
visits_2_weeks['lifetime'] = (visits_2_weeks.session_start.astype('datetime64[D]') -
visits_2_weeks.first_visit +
timedelta(days=1)
).dt.days
visits_2_weeks.query('lifetime <= 14', inplace=True)
visits_2_weeks.loc[:, 'session_start'] = visits_2_weeks.loc[:, 'session_start'].astype('datetime64[D]')
visits_2_weeks.head()
| user_id | region | device | channel | session_start | session_end | session_duration | first_visit | lifetime | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | 599326 | United States | Mac | FaceBoom | 2019-05-07 | 2019-05-07 21:11:36 | 0 days 00:12:39 | 2019-05-07 | 1 |
| 1 | 599326 | United States | Android | FaceBoom | 2019-05-08 | 2019-05-08 06:52:28 | 0 days 00:51:46 | 2019-05-07 | 2 |
| 2 | 599326 | United States | Mac | FaceBoom | 2019-05-09 | 2019-05-09 08:40:06 | 0 days 01:06:50 | 2019-05-07 | 3 |
| 3 | 4919697 | United States | iPhone | FaceBoom | 2019-07-09 | 2019-07-09 14:44:32 | 0 days 01:58:25 | 2019-07-09 | 1 |
| 4 | 4919697 | United States | iPhone | FaceBoom | 2019-07-10 | 2019-07-10 23:00:37 | 0 days 00:33:13 | 2019-07-09 | 2 |
# добавим к сессиям данные о заказах.
# для каждого дня посчитаем сумму покупок.
# подставим суммы в дни сессий.
# сессии без покупок будут нулями. дни без сессий – пропусками данных
user_orders = (orders
.pivot_table(index=['user_id',orders.event_dt.astype('datetime64[D]')],
values='revenue',
aggfunc='sum')
.reset_index()
.rename(columns={'event_dt': 'session_start'})
)
_visits_2_weeks = (visits_2_weeks
.pivot_table(index=['user_id', 'session_start'],
aggfunc={'lifetime': 'first'})
.reset_index()
)
visits_orders = (_visits_2_weeks
.merge(user_orders,
how='left',
on=['user_id', 'session_start'])
.fillna(0)
.pivot_table(index='user_id',
columns='lifetime',
values='revenue',
aggfunc='sum')
.reset_index()
)
visits_orders.head()
| lifetime | user_id | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 599326 | 4.99 | 4.99 | 4.99 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 1 | 4919697 | 0.00 | 0.00 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 2 | 6085896 | 0.00 | NaN | 0.00 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 3 | 22593348 | 0.00 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 4 | 31989216 | 0.00 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
# добавим данные о сессиях и покупках в нашу сводную таблицу
users = users.merge(visits_orders, how='left', on='user_id', validate='1:1')
# ради интереса подклеим к юзерам ещё полную сумму их покупок без ограничения в 2 недели
users = (users
.merge(orders
.groupby('user_id')
.agg({'revenue': 'sum'})
.reset_index(),
how='left',
on='user_id')
.rename(columns={'revenue': 'total_ltv'})
)
users.total_ltv = users.total_ltv.fillna(0)
Ну что ж – основные данные подготовлены. Добавим пару метрик и приступим к исследовательскому анализу данных.
users['ltv'] = users.loc[:, 1:14].sum(axis=1)
users['is_payer'] = users.ltv > 0
# уберём лишнее
del _visits_2_weeks, visits_orders, user_orders, cac_by_channel
users.head()
| user_id | channel | device | region | first_visit | cac | 1 | 2 | 3 | 4 | ... | 8 | 9 | 10 | 11 | 12 | 13 | 14 | total_ltv | ltv | is_payer | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 599326 | FaceBoom | Mac | United States | 2019-05-07 | 1.09 | 4.99 | 4.99 | 4.99 | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 14.97 | 14.97 | True |
| 1 | 4919697 | FaceBoom | iPhone | United States | 2019-07-09 | 1.11 | 0.00 | 0.00 | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.00 | 0.00 | False |
| 2 | 6085896 | organic | iPhone | France | 2019-10-01 | 0.00 | 0.00 | NaN | 0.00 | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.00 | 0.00 | False |
| 3 | 22593348 | AdNonSense | PC | Germany | 2019-08-22 | 0.99 | 0.00 | NaN | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.00 | 0.00 | False |
| 4 | 31989216 | YRabbit | iPhone | United States | 2019-10-02 | 0.23 | 0.00 | NaN | NaN | NaN | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.00 | 0.00 | False |
5 rows × 23 columns
Посмотрим, из каких стран приходили пользователи в течение периода исследования. Какая у нас итоговая разбивка. Сразу поглядим, сколько у нас платящих пользователей.
# Напишем функцию для построения графика истории распределения пользователей по параметру.
def show_param_dynamic(param):
fig, ax = plt.subplots(figsize=(15, 10))
(users
.pivot_table(index='first_visit',
# .pivot_table(index=visits_2_weeks.session_start.astype('datetime64[D]'),
columns=[param, 'is_payer'],
values='user_id',
aggfunc='count')).plot(ax=ax, kind='area', stacked=True, cmap='Paired')
# ax.legend(bbox_to_anchor=(1.1, 1))
ax.legend(loc='upper right')
ax.set_title(f'Распределение привлеченных пользователей по параметру {param}')
ax.set_ylabel('Новых уникальных пользователей за сутки')
ax.set_xlabel('Дата привлечения')
ax.grid()
plt.tight_layout()
plt.show()
# и функцию для построения накопленного распределения
# для удобства подпишем долю плательщиков в группе
def show_param_abs(param):
_distrib = (users
.pivot_table(index=param,
columns='is_payer',
values='user_id',
aggfunc='count')
.rename(columns={True: 'payer', False: 'not_payer'})
.sort_values(by='payer')
)
_distrib['sum'] = _distrib.sum(axis=1)
_distrib['payer_share'] = _distrib.payer / _distrib['sum']
fig, ax = plt.subplots(figsize=(10,len(_distrib)*0.7))
_distrib[['payer', 'not_payer']].plot(kind='barh', stacked=True, ax=ax, cmap='Set1')
_, xmax = plt.xlim()
plt.xlim(0, xmax+3000)
sum_share = _distrib[['sum', 'payer_share']].to_records(index=False)
for i, line in enumerate(sum_share):
ax.text(line[0] + 1000,
i,
f'{line[1]:.2%}',
color='black', fontsize=12, ha='left', va='center')
ax.grid(axis='x')
ax.set_ylabel('')
ax.set_title(f'Привлеченные пользователи в разрезе по параметру {param}, доля плательщиков')
plt.tight_layout()
plt.show()
show_param_dynamic('region')
show_param_abs('region')
Посмотрим, на распределение по устройствам.
show_param_dynamic('device')
show_param_abs('device')
show_param_dynamic('channel')
show_param_abs('channel')
Последний пункт рассмотрим чуть подробнее.
# построим графики доли органики и всех новых пользователей по дням
organic = users.query('channel == "organic"').pivot_table(index='first_visit', aggfunc='nunique', values='user_id')
total_new = users.pivot_table(index='first_visit', aggfunc='nunique', values='user_id')
organic_share = (organic / total_new).rename(columns={'user_id': 'Доля органических пользователей'})
organic.rename(columns={'user_id': 'Количество органических пользователей'}, inplace=True)
fig, ax = plt.subplots()
ax2 = ax.twinx()
organic_share.plot(ax=ax, color='red', legend=False)
organic.plot(ax=ax2)
lines, labels = ax.get_legend_handles_labels()
lines2, labels2 = ax2.get_legend_handles_labels()
ax2.legend(lines + lines2, labels + labels2, loc='lower left')
ax.set_ylim(0,)
ax2.set_ylim(0,)
ax.grid()
ax.set_title('Приток органических пользователей: доля от всех и количество по дням')
ax.set_xlabel('День привлечения')
ax.set_ylabel('Доля органики')
ax2.set_ylabel('Количество органических пользователей')
# ax.set_xlabel('День привлечения пользователей')
ax.yaxis.set_major_formatter(StrMethodFormatter('{x:,.0%}'))
plt.show()
Да – мы не ошиблись. Доля органических пользователей начиная с июня упала почти вдвое. И не потому что мы включили рекламу и стало больше рекламных пользователей – количество органических пользователей в абсолютах тоже сократилось.
Хорошо бы понять, почему – ушли любители новинок, закончился эффект какой-то рекламной кампании или же мы что-то поменяли в игре. Органика - это бесплатные деньги, так то терять эту группу совсем нехорошо.
И чтобы лучше себе представлять положение дел, построим график суточной аудитории проекта. И добавим на него суточную разницу между суммой покупок и расходами на привлечение пользователей.
history = pd.concat((orders.groupby(orders.event_dt.dt.date).agg({'user_id': 'nunique'}),
orders.groupby(orders.event_dt.dt.date).agg({'revenue': 'sum'}),
visits.groupby(visits.session_start.dt.date).agg({'user_id': 'nunique'}),
costs.groupby(costs.dt.dt.date).agg({'costs': 'sum'})),
axis=1
)
history.columns = ['payers', 'revenue', 'total', 'costs']
history['profit'] = history.revenue - history.costs
history['non_payers'] = history.total - history.payers
# построим графики доли органики и всех новых пользователей по дням
fig, ax = plt.subplots()
ax2 = ax.twinx()
history[['payers', 'non_payers']].plot(kind='area', stacked=True, ax=ax, legend=False, cmap='tab10')
history.profit.plot(ax=ax2, color='red')
lines, labels = ax.get_legend_handles_labels()
lines2, labels2 = ax2.get_legend_handles_labels()
ax2.legend(lines + lines2, labels + labels2, loc='upper left')
ax.set_ylim(0,)
ax2.set_ylim(0,)
ax.grid()
ax.set_title('Суточная аудитория и доход (оборот - расходы на привлечение)')
ax.set_xlabel('')
ax.set_ylabel('Уникальных пользователей в сутки')
ax2.set_ylabel('Доход')
ax2.yaxis.set_major_formatter(StrMethodFormatter('{x:,.0f}$'))
plt.show()
Мы не знаем, сумму остальных издержек, поэтому не можем по этим данным судить о прибыльности всего проекта, но, по крайней мере, маркетинговые расходы на всём периоде мы окупаем ))
Посчитаем основные метрики по трём срезам: каналы привлечения, страны, устройства. Судя по тому, что мы видели на предыдущих графиках, нас в первую очередь интересуют срез по каналам привлечения.
Будем смотреть данные по трём срезам – так что все таблицы и графики завернём в функции.
# напишем вспомогательные функции для расчёта сводной таблицы по срезу
# считаем топовое значение параметра и его долю
def top(serie):
mode = stats.mode(serie)
name = mode[0][0]
share = mode[1][0] / len(serie)
return f'{name}: {share:.0%}'
# считаем ретеншн
def retention(serie):
return serie.count() / len(serie)
# и функции для форматирования
def style_negative(v, props=''):
return props if type(v) is float and v < 0 else None
def style_low_roi(v, props=''):
return props if v < 1 else None
# запилим функцию для сводной таблицы по срезу
def show_pivot(param):
money_pivot = (users
.rename(columns={14: '14'})
.pivot_table(index=param,
aggfunc={'cac': 'sum',
'ltv': 'sum',
'user_id': 'count',
'is_payer': 'mean',
'region': lambda x: top(x),
'device': lambda x: top(x),
'channel': lambda x: top(x),
'total_ltv': 'sum',
'14': lambda x: x.count() / len(x)
})
.rename(columns={'ltv': 'revenue_2w',
'total_ltv': 'revenue_full',
'cac': 'costs',
'is_payer': 'payer_share',
'user_id': 'user_count',
'region': 'top_region',
'device': 'top_device',
'channel': 'top_channel',
'14': 'retention_2w'
}
)
)
# добавим ещё показателей
money_pivot['roi_2w'] = (money_pivot.revenue_2w / money_pivot.costs).replace(np.inf, np.nan)
money_pivot['profit_2w'] = money_pivot.revenue_2w - money_pivot.costs
money_pivot['profit_full'] = money_pivot.revenue_full - money_pivot.costs
money_pivot['ltv_2w'] = money_pivot.revenue_2w / money_pivot.user_count
money_pivot['cac'] = money_pivot.costs / money_pivot.user_count
# выставим порядок столбцов и сортировку
money_pivot = (money_pivot[['user_count',
'top_region',
'top_device',
'top_channel',
'retention_2w',
'payer_share',
'cac',
'ltv_2w',
'roi_2w',
'costs',
'revenue_2w',
'profit_2w',
'revenue_full',
'profit_full']]
.sort_values(by='profit_2w', ascending=False)
)
# посчитаем ИТОГО
totals = (users
.agg({'cac': 'sum',
'ltv': 'sum',
'total_ltv': 'sum',
'user_id': 'count',
'is_payer': 'mean'})
.to_frame()
.T
.rename(columns={'ltv': 'revenue_2w',
'total_ltv': 'revenue_full',
'cac': 'costs',
'is_payer': 'payer_share',
'user_id': 'user_count'})
)
totals['cac'] = totals.costs / totals.user_count
totals['ltv_2w'] = totals.revenue_2w / totals.user_count
totals['roi_2w'] = totals.revenue_2w / totals.costs
totals['profit_2w'] = totals.revenue_2w - totals.costs
totals['profit_full'] = totals.revenue_full - totals.costs
totals['top_region'] = top(users.region.to_list())
totals['top_device'] = top(users.device.to_list())
totals['top_channel'] = top(users.channel.to_list())
totals['retention_2w'] = users[14].count() / len(users)
# добавим ИТОГО в сводную
money_pivot = (pd
.concat([money_pivot, totals])
.reset_index()
.rename(columns={'index': param})
)
money_pivot.iloc[-1, 0] = 'TOTALS'
# отобразим таблицу с форматированием
display(money_pivot.drop(columns=['top_'+param])
.style
.format({'user_count': "{:_.0f}",
'ltv_2w': "${:.2f}",
'costs': "${:_.0f}",
'cac': "${:_.2f}",
'payer_share': "{:.1%}",
'retention_2w': "{:.1%}",
'revenue_2w': "${:_.0f}",
'revenue_full': "${:_.0f}",
'profit_2w': "${:_.0f}",
'profit_full': "${:_.0f}",
'roi_2w': "{:.2f}"})
.background_gradient(cmap='Greens',
axis=0,
subset=pd.IndexSlice[money_pivot.index[0:-1],
['user_count',
'retention_2w',
'payer_share',
'ltv_2w',
'roi_2w',
'revenue_2w',
'profit_2w',
'revenue_full',
'profit_full']
]
)
.background_gradient(cmap='Reds',
axis=0,
subset=pd.IndexSlice[money_pivot.index[0:-1],
['cac',
'costs']
]
)
.applymap(style_negative, props='color:red;')
.applymap(style_low_roi, props='color:red;', subset='roi_2w')
.set_properties(**{'background-color': '#ffffb3'}, subset=pd.IndexSlice[money_pivot.index[-1], :])
)
# функция для графика истории рои
# с ручкой для дополнительного условия
# и возможносью указать размер окна сглаживания
def show_2w_roi_story(parameter, condition='', window=7):
fig, ax = plt.subplots(figsize=(12, 6))
_users = users.query('channel != "organic"' + condition)
params = _users[parameter].unique()
for param in params:
part = _users[_users[parameter] == param]
part = part.groupby('first_visit').agg({'ltv': sum, 'cac': sum})
(part.ltv / part.cac).rolling(window).mean().plot(ax=ax, label=param)
xmin, xmax = ax.get_xlim()
ax.hlines(1, xmin, xmax, colors='red', linestyles='--')
ax.legend()
ax.grid()
ax.set_ylabel('Окупаемость расходов')
ax.set_xlabel('День привлечения пользователей')
ax.set_title(f'Окупаемость расходов на привлечение через 2 недели у пользователей разных когорт. Сглаживание - {window}')
plt.tight_layout()
plt.show()
# функция для отрисовки рои по первым 14 дням с возможностью добавить условие
def show_roi_by_lifetime(parameter, condition=''):
fig, ax = plt.subplots(figsize=(12, 6))
_users = users.query('channel != "organic"' + condition)
params = _users[parameter].unique()
for param in params:
part = _users[_users[parameter] == param]
(part
# .query('channel == "organic"')
.loc[:, 1:14]
.fillna(0)
.cumsum(axis=1)
# # .div(users.loc[:5, 'cac'], axis=0)
.T
.unstack()
.reset_index()
.rename(columns={'level_0': 'id', 'level_1': 'lifetime', 0: param})
.pivot_table(index='lifetime', aggfunc='sum', values=param)
# - part.cac.sum()
.div(part.cac.sum())
).plot(ax=ax)
ax.hlines(1, 1, 14, colors='red', linestyles='--')
ax.grid()
ax.set_ylabel('Окупаемость расходов')
ax.set_xlabel('Дни жизни пользователя')
ax.set_xlim(1, 14)
ax.set_title(f'Окупаемость расходов на привлечение в разрезе по параметру {parameter}')
plt.tight_layout()
plt.show()
# функция для графика ретеншна с возможностью добавить условие
def show_retention(param, condition=''):
fig, (ax1, ax2) = plt.subplots(1, 2, figsize=(16, 6))
for ax, payer in zip((ax1, ax2), ['is_payer', '~is_payer']):
(users
.query(payer + condition)
.groupby(param)[list(range(1, 15))]
.apply(lambda x: x.count() / len(x))
.loc[:, 2:14]
.T
.plot(ax=ax)
)
ax.grid()
ax.set_xlabel('Дни жизни пользователей')
ax.set_ylim(0, 0.7)
ax.legend(loc='upper right')
ax.yaxis.set_major_formatter(StrMethodFormatter('{x:,.0%}'))
ax1.set_title('Удержание пользователей. Платящие пользователи')
ax2.set_title('Удержание пользователей. Неплатящие пользователи')
# axes[1].sharey(axes[0])
plt.show()
# фунцкия для графика истории ретеншна с возможностью добавить условие и размер окна сглаживания
def show_retention_history(param, condition='', window=7):
fig, ax = plt.subplots(figsize=(12, 6))
(users
.query('channel == channel' + condition)
.pivot_table(index=param,
columns='first_visit',
values=14,
aggfunc=lambda x: x.count() / len(x))
.T
.rolling(window).mean()
).plot(ax=ax)
ax.grid()
ax.set_title(f'Удержание пользователей через 2 недели. Усреднение – {window}')
ax.set_xlabel('День привлечения пользователей')
ax.yaxis.set_major_formatter(StrMethodFormatter('{x:,.1%}'))
plt.show()
# история двухнедельного ltv платящих пользователей
def show_payer_ltv_history(param, condition='', window=7):
fig, ax = plt.subplots(figsize=(12, 6))
(users
.query('is_payer' + condition)
.pivot_table(index='first_visit',
values='ltv',
columns=param,
aggfunc='mean')
.rolling(window)
.mean()
.plot(ax=ax))
ax.set_ylim(0,)
ax.grid()
ax.legend(loc='upper right')
ax.set_title(f'Средний LTV платящих пользователей через 2 недели после привлечения, сглаживание – {window}')
ax.set_ylabel('Средний LTV')
ax.set_xlabel('День привлечения пользователей')
ax.yaxis.set_major_formatter(StrMethodFormatter('{x:,.0f}$'))
plt.show()
# история двухнедельной конверсии пользователей
def show_user_conversion_history(param, condition='', window=7):
fig, ax = plt.subplots(figsize=(12, 6))
(users
.query('channel == channel' + condition)
.pivot_table(index='first_visit',
values='is_payer',
columns=param,
aggfunc='mean')
.rolling(window)
.mean()
.plot(ax=ax))
ax.set_ylim(0,)
ax.grid()
ax.legend(loc='upper right')
ax.set_title(f'Средняя конверсия в платящих пользователей через 2 недели после привлечения, сглаживание – {window}')
ax.set_ylabel('Доля платящих пользователей')
ax.set_xlabel('День привлечения пользователей')
ax.yaxis.set_major_formatter(StrMethodFormatter('{x:,.0%}'))
plt.show()
# таблица отсортирована по убыванию двухнедельного дохода
show_pivot('channel')
| channel | user_count | top_region | top_device | retention_2w | payer_share | cac | ltv_2w | roi_2w | costs | revenue_2w | profit_2w | revenue_full | profit_full | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | organic | 53_653 | United States: 68% | iPhone: 36% | 1.0% | 2.0% | $0.00 | $0.31 | nan | $0 | $16_695 | $16_695 | $45_903 | $45_903 |
| 1 | WahooNetBanner | 8_029 | France: 35% | PC: 40% | 1.9% | 5.2% | $0.60 | $0.90 | 1.50 | $4_832 | $7_240 | $2_407 | $19_647 | $14_815 |
| 2 | RocketSuperAds | 4_225 | United States: 100% | iPhone: 44% | 2.3% | 7.3% | $0.42 | $0.91 | 2.19 | $1_766 | $3_860 | $2_093 | $11_404 | $9_637 |
| 3 | lambdaMediaAds | 2_049 | UK: 35% | PC: 40% | 2.9% | 10.4% | $0.73 | $1.73 | 2.38 | $1_490 | $3_552 | $2_062 | $8_907 | $7_417 |
| 4 | LeapBob | 8_047 | UK: 36% | PC: 40% | 1.3% | 2.9% | $0.21 | $0.46 | 2.21 | $1_690 | $3_733 | $2_043 | $9_320 | $7_629 |
| 5 | YRabbit | 4_077 | United States: 100% | iPhone: 43% | 1.1% | 3.7% | $0.22 | $0.55 | 2.53 | $885 | $2_236 | $1_351 | $6_056 | $5_171 |
| 6 | MediaTornado | 4_148 | United States: 100% | iPhone: 44% | 0.9% | 3.4% | $0.22 | $0.53 | 2.43 | $909 | $2_213 | $1_304 | $5_638 | $4_728 |
| 7 | OppleCreativeMedia | 8_078 | UK: 35% | PC: 40% | 1.2% | 2.6% | $0.25 | $0.41 | 1.63 | $2_018 | $3_290 | $1_272 | $8_181 | $6_163 |
| 8 | AdNonSense | 3_687 | France: 35% | PC: 40% | 0.8% | 10.8% | $1.01 | $0.84 | 0.83 | $3_716 | $3_087 | $-629 | $3_523 | $-193 |
| 9 | FaceBoom | 27_349 | United States: 100% | iPhone: 45% | 0.6% | 11.9% | $1.11 | $0.82 | 0.74 | $30_451 | $22_420 | $-8_031 | $24_202 | $-6_249 |
| 10 | TipTop | 18_367 | United States: 100% | iPhone: 45% | 2.7% | 9.3% | $2.75 | $1.46 | 0.53 | $50_541 | $26_803 | $-23_738 | $68_266 | $17_726 |
| 11 | TOTALS | 141_709 | United States: 67% | iPhone: 36% | 1.3% | 5.7% | $0.69 | $0.67 | 0.97 | $98_299 | $95_127 | $-3_171 | $211_045 | $112_746 |
# похоже, что каналы у нас имеют региональную специализацию. проверим
(
(users
.pivot_table(index='channel',
columns='region',
aggfunc={'user_id': 'count'})
.sort_values(by=('user_id', 'United States'), ascending=False))
/
(users
.pivot_table(columns='region',
aggfunc={'user_id': 'count'})
.values)
).style.format('{:.0%}').background_gradient(cmap='Greens', axis=0).highlight_min(color='lightgrey')
| user_id | ||||
|---|---|---|---|---|
| region | France | Germany | UK | United States |
| channel | ||||
| organic | 37% | 37% | 37% | 38% |
| FaceBoom | nan% | nan% | nan% | 29% |
| TipTop | nan% | nan% | nan% | 19% |
| RocketSuperAds | nan% | nan% | nan% | 4% |
| MediaTornado | nan% | nan% | nan% | 4% |
| YRabbit | nan% | nan% | nan% | 4% |
| AdNonSense | 8% | 8% | 7% | nan% |
| LeapBob | 17% | 17% | 17% | nan% |
| OppleCreativeMedia | 17% | 17% | 17% | nan% |
| WahooNetBanner | 17% | 17% | 17% | nan% |
| lambdaMediaAds | 4% | 4% | 4% | nan% |
Опишем, что мы видим:
В целом сводная таблица нам всё показала. Ещё посмотрим на графики, чтобы убедиться, что мы всё правильно поняли.
param = 'channel'
# чтобы не было месива из кривых разделим их на две пачки и выберем только самых интересных
US = ['FaceBoom', 'TipTop', 'RocketSuperAds', 'YRabbit']
EU = ['AdNonSense', 'LeapBob', 'WahooNetBanner', 'lambdaMediaAds']
show_roi_by_lifetime(param)
show_2w_roi_story(param, ' and channel in @US')
show_2w_roi_story(param, ' and channel in @EU')
show_payer_ltv_history(param, ' and channel in @US', 3)
show_payer_ltv_history(param, ' and channel in @EU', 3)
show_retention(param)
show_retention_history(param, ' and channel in @US')
show_retention_history(param, ' and channel in @EU')
show_user_conversion_history(param, ' and channel in @US')
show_user_conversion_history(param, ' and channel in @EU')
Графики подтверждают наши выводы, сделанные из таблицы. Динамика всех показателей скорее стабильная – у больших каналов нет резких заметных изменений. У мелких каналов бывают взлёты – какие-то особенно удачные когорты.
Из интересного – у канала TipTop в самом начале истории был положительный двухнедельный ROI – в тот же период показатель LTV был тоже чуть повыше для канала, но, видимо, основной эффект дал низкий CAC – стоит узнать подробнее, что это было – какие-то скидки для первых заказов или что-то другое – вдруг удастся повторить.
fig, ax = plt.subplots(figsize=(12, 6))
(users
.pivot_table(index='first_visit',
columns='channel',
values='cac',
aggfunc='mean')
.plot(ax=ax)
)
ax.grid()
# ax.legend(loc='upper right')
ax.legend(bbox_to_anchor=(1.02, 1))
ax.set_title(f'Стоимость привлечения пользователя по каналам')
ax.set_xlabel('День привлечения пользователей')
ax.yaxis.set_major_formatter(StrMethodFormatter('{x:,.1f}$'))
plt.show()
Ну да – так и есть – стоимость привлечения из TipTop выросла в три с половиной раза – хорошо бы выяснить, почему. И вернуть, как было в начале )))
Посмотрим на срез по странам. Но, кажется, что статистика по странам во многом зависит от каналов: США – будут отражением ситуации с TipTop и FaceBoom, а Европа – своих каналов.
show_pivot('region')
| region | user_count | top_device | top_channel | retention_2w | payer_share | cac | ltv_2w | roi_2w | costs | revenue_2w | profit_2w | revenue_full | profit_full | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | UK | 16_609 | PC: 41% | organic: 37% | 1.3% | 3.8% | $0.29 | $0.58 | 2.03 | $4_764 | $9_671 | $4_907 | $24_262 | $19_499 |
| 1 | Germany | 14_146 | PC: 40% | organic: 37% | 1.3% | 4.0% | $0.29 | $0.57 | 1.94 | $4_170 | $8_076 | $3_907 | $19_358 | $15_189 |
| 2 | France | 16_524 | PC: 40% | organic: 37% | 1.3% | 3.7% | $0.29 | $0.52 | 1.77 | $4_813 | $8_510 | $3_697 | $21_166 | $16_353 |
| 3 | United States | 94_430 | iPhone: 45% | organic: 38% | 1.3% | 6.7% | $0.90 | $0.73 | 0.81 | $84_553 | $68_870 | $-15_683 | $146_259 | $61_707 |
| 4 | TOTALS | 141_709 | iPhone: 36% | organic: 38% | 1.3% | 5.7% | $0.69 | $0.67 | 0.97 | $98_299 | $95_127 | $-3_171 | $211_045 | $112_746 |
# похоже, что распределение по платформам в разных странах заметно различается. проверим
(
(users
.pivot_table(index='device',
columns='region',
aggfunc={'user_id': 'count'})
.sort_values(by=('user_id', 'United States'), ascending=False))
/
(users
.pivot_table(columns='region',
aggfunc={'user_id': 'count'})
.values)
).style.format('{:.0%}').background_gradient(cmap='Greens', axis=0)
| user_id | ||||
|---|---|---|---|---|
| region | France | Germany | UK | United States |
| device | ||||
| iPhone | 20% | 20% | 20% | 45% |
| Mac | 11% | 11% | 10% | 25% |
| Android | 30% | 30% | 29% | 20% |
| PC | 40% | 40% | 41% | 10% |
Что видим:
США - рынок, на котором у нас больше всего пользователей,– на 2-недельном отрезке приносит нам убытки. Мы уже выяснили выше, что это проблема каналов привлечения – для основных каналов, приносящих нам американских пользователей, у нас не сходится экономика.
При этом интересно, что конверсия на американском рынке – заметно выше европейской, а ретеншн такой же. Проблема – в стоимости привлечения – она слишком высокая – втрое выше, чем в среднем по Европе.
Доля органики у нас одинаковая во всех регионах. Наверно это скорее обнадеживающий факт - значит, ни один из рекламных каналов как-то особенно сильно не влияет на органику – а значит, отказавшись от кого-то из рекламодателей – например, от FaceBoom – мы не просядем в органике.
США отличаются от Европы по распределению платформ. В Европе лидирует PC и Android. В США – iPhone и Mac. Поскольку в Европе дела у нас обстоят лучше, чем в США то, вероятно, и на PC-Android дела будут лучше, чем на iPhone-Mac
param = 'region'
show_roi_by_lifetime(param)
show_2w_roi_story(param, ' and channel in @US')
show_2w_roi_story(param, ' and channel in @EU')
show_payer_ltv_history(param, ' and channel in @US', 3)
show_payer_ltv_history(param, ' and channel in @EU', 3)
show_retention(param)
show_retention_history(param, ' and channel in @US')
show_retention_history(param, ' and channel in @EU')
show_user_conversion_history(param, ' and channel in @US')
show_user_conversion_history(param, ' and channel in @EU')
Посмотрим на данные и попробуем выяснить, что важнее – платформа пользователя или страна и рекламный канал происхождения.
show_pivot('device')
| device | user_count | top_region | top_channel | retention_2w | payer_share | cac | ltv_2w | roi_2w | costs | revenue_2w | profit_2w | revenue_full | profit_full | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | PC | 28_791 | United States: 34% | organic: 37% | 1.4% | 4.8% | $0.50 | $0.63 | 1.27 | $14_262 | $18_180 | $3_918 | $42_376 | $28_115 |
| 1 | Android | 33_070 | United States: 58% | organic: 38% | 1.2% | 5.7% | $0.63 | $0.67 | 1.06 | $20_921 | $22_171 | $1_250 | $50_339 | $29_418 |
| 2 | Mac | 28_431 | United States: 83% | organic: 38% | 1.4% | 6.1% | $0.79 | $0.69 | 0.87 | $22_504 | $19_626 | $-2_879 | $41_993 | $19_488 |
| 3 | iPhone | 51_417 | United States: 82% | organic: 38% | 1.2% | 6.0% | $0.79 | $0.68 | 0.87 | $40_612 | $35_151 | $-5_461 | $76_337 | $35_725 |
| 4 | TOTALS | 141_709 | United States: 67% | organic: 38% | 1.3% | 5.7% | $0.69 | $0.67 | 0.97 | $98_299 | $95_127 | $-3_171 | $211_045 | $112_746 |
Из-за того, что устройства регионо-специфичны, общий срез по устройствам, как мы и говорили, отражает проблемы американского рынка. Основные для США платформы приносят нам убыток. Популярные в Европе устройства – прибыль.
param = 'device'
show_roi_by_lifetime(param)
show_2w_roi_story(param)
show_payer_ltv_history(param, window=3)
show_retention(param)
show_retention_history(param)
show_user_conversion_history(param)
Мы хотели выяснить, не происходило ли у нас на сервисе каких-то технических изменений, которые могли отрицательно повлиять на конверсию и/или удержание пользователей. Но графики по этим двум показателям не показывают каких-то резких однонаправленных изменений. В целом всё колеблется в одних и тех же рамках. Вероятно, серьёзных ухудшений или улучшений не случалось (что не означает, что проблем нет – может, они просто монотонны).
Попробуем выяснить, есть ли разница между пользователями на iPhone-Mac и Android-PC. Может быть, проблема не в FaceBooom, а в том, что оттуда приходят Applе-пользователи, которые нам плохо подходят. Сравним, различаются ли показатели на разных платформах в рамках одного канала. Возьмём для сравнения неуспешный американский FaceBoom и хороший европейский LeapBob.
param = 'device'
faceboom = ' and channel == "FaceBoom"'
leapbob = ' and channel == "LeapBob"'
print('FaceBoom')
show_retention(param, faceboom)
print('LeapBob')
show_retention(param, leapbob)
print()
print('FaceBoom')
show_roi_by_lifetime(param, faceboom)
print('LeapBob')
show_roi_by_lifetime(param, leapbob)
# show_retention_history(param)
# show_user_conversion_history(param)
FaceBoom
LeapBob
FaceBoom
LeapBob
Нуу, по ретеншн разницы нет. На LeapBob явно меньше данных, поэтому там биение есть, но в целом, кажется, одинаково.
А вот по ROI – по сути это LTV, так как цена привлечения в рамках канала одинаковая – разница между платформами есть. Но в разных странах лидеры разные. В США по двухнедельному доходу лидиируют Android и Mac. В Европе – сильно хуже других iPhone. Но эти различия всё равно слабее, чем общая тенденция в канале.
Вероятно, эти различия в платформах можно использовать, чтобы немного усилить прибыльность привлекаемых пользователей – например, в Европе больше денег тратить на привлечение пользователей на Mac. Но главную проблему – убыточность американских каналов – это не решит.
Основное исследование проводилось с горизонтом в 2 недели. Некоторые показатели посчитали в целом за весь период данных (май-октябрь 2019 года).
Основной источник финансовых потерь – пользователи, привлеченные на американский рынок с каналов FaceBoom (убыточен на всём периоде) и TipTop (на большом периоде – показывает прибыль).
Проблема этих каналов – высокая стоимость привлечения – на двухнедельном периоде она больше, чем успевают заплатить привлеченные пользователи. Стоимость привлечения c TipTop за рассматриваемый период выросла в 3,5 раза.
В случае с FaceBoom проблема ещё и в плохом удержании пользователей – они не успевают прожить достаточно долго, чтобы окупить своё привлечение. Пользователи канала TipTop на периоде больше, чем две недели, приносят прибыль.
Вероятно, причина именно в качестве пользователей, привлекаемых с FaceBoom, – другие факторы регион или платформа пользователей – не влияют. Пользователи, приходящие с FaceBoom, относительно часто совершают покупки, но быстро уходят.
У проекта значительная доля органических пользователей – 38% (хотя их доля и значительно сократилась начиная с июня). Эта группа приносит 40% всей прибыли. При этом показатель конверсии в этой группе значительно ниже, чем в других.